<?php
//excel操作相关

namespace App\Services\Common;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

//use Maatwebsite\Excel\Facades\Excel;

class ExcelOperateService
{

    public $importUrl;
    public static $exportUrl = 'docs/imports/';

    public function __construct() {
        $this->importUrl = base_path() . '/storage/docs/imports/';
    }

    /**
     * 读取表格数据
     * @param string 临时文件路径
     * @return array
     */
    public static function readExcel($file)
    {
        $fileNameSplit = explode('.', $file);
        $fileSuffix = end($fileNameSplit);
        switch ($fileSuffix) {
            case 'xls':
                $readerType = 'Xls';
                break;
            case 'xlsx':
                $readerType = 'Xlsx';
                break;
        }
        // $spreadsheet = $reader->load($fileName);
        $reader = IOFactory::createReader($readerType);
        $spreadsheet = $reader->load($file);
        // $reader->setReadDataOnly(true); // 设置后无法获取excel中的图片

        $worksheet = $spreadsheet->getActiveSheet();
        // $worksheet   = $spreadsheet->getSheetByName('testcase');
        // $rawCasedata = $worksheet->toArray();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数

        $excelData = [];
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $excelData[$row][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
        }
        if(!$excelData) {
            return [];
        }
        $output = [];
        $titles = array_shift($excelData);
        foreach($excelData as $val) {
            $item = [];
            foreach($titles as $tk => $tVal) {
                $item[$tVal] = $val[$tk] ?? '';
            }
            $output[] = $item;
        }
        unset($excelData);

        return $output;
    }


    // 导出
    public static function exportDownloadExcel($header = [], $type = true, $data = [], $fileName = "1910")
    {
        // 实例化类
        $preadsheet = new Spreadsheet();
        // 创建sheet
        $sheet = $preadsheet->getActiveSheet();
        // 循环设置表头数据
        foreach ($header as $k => $v) {
            $sheet->setCellValue($k, $v);
        }
        // 生成数据
        $sheet->fromArray($data, null, "A2");
        $rowI = 2;
        foreach($data as $k => $v) {
            $i = 0;
            foreach ($header as $hk => $hv) {
                $setV = $v[$i] ?? '';
                $fileRow = $hk[0] . $rowI;
                $sheet->setCellValueExplicit($fileRow, $setV, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $i++;
            }
            $rowI++;
        }
        // 样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);
        // 设置下载与后缀
        if ($type) {
            header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            $type = "Xlsx";
            $suffix = "xlsx";
        } else {
            header("Content-Type:application/vnd.ms-excel");
            $type = "Xls";
            $suffix = "xls";
        }
        ob_end_clean();//清楚缓存区
        // 激活浏览器窗口
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        header("Content-Disposition:attachment;filename=$encoded_filename.$suffix");
        //缓存控制
        header("Cache-Control:max-age=0");
        // 调用方法执行下载
        $writer = IOFactory::createWriter($preadsheet, $type);
        // 数据流
        $writer->save("php://output");
    }


}
